package tipo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import util.Conexao;

public class RepositorioTipoBanco  implements ITipo{
	private Connection con;
	
	
	@Override
	public void adicionar(Tipo tipo) throws ClassNotFoundException, SQLException {
		 con = Conexao.pegarConexao();
		 String sql = "insert into tipo (nome) values (?)";
		 PreparedStatement ps = con.prepareStatement(sql);
		 ps.setString(1, tipo.getNome());
		 ps.execute();
	}

	@Override
	public void editar(Tipo tipo) throws ClassNotFoundException, SQLException {
		 con = Conexao.pegarConexao();
		 String sql = "update tipo set nome = ? where id = ?;";
		 PreparedStatement ps = con.prepareStatement(sql);
		 ps.setInt(2, tipo.getId());
		 ps.setString(1, tipo.getNome());
		 ps.execute();
		
	}

	@Override
	public void remover(int idTipo) throws ClassNotFoundException, SQLException {
		 con = Conexao.pegarConexao();
		 String sql = "delete from tipo  where id = ?;";
		 PreparedStatement ps = con.prepareStatement(sql);
		 ps.setInt(1, idTipo);
		 ps.execute();
	}

	@Override
	public int existe(int idTipo) throws ClassNotFoundException, SQLException {
		 int res = 0;
		 con = Conexao.pegarConexao();
		 String sql = "select * from tipo where id = ?;";
		 PreparedStatement ps = con.prepareStatement(sql);
		 ps.setInt(1, idTipo);
		 ps.execute();
		 if(ps.getResultSet().next()){
			 res = 1;
		 }
		 return res;
	}

	@Override
	public Tipo pesquisar(int idTipo) throws ClassNotFoundException, SQLException {
		 con = Conexao.pegarConexao();
		 String sql = "select * from tipo where id = ?;";
		 PreparedStatement ps = con.prepareStatement(sql);
		 ps.setInt(1, idTipo);
		 ps.execute();
		 ResultSet rs = ps.getResultSet();
		 Tipo t = null;
		 while(rs.next()){
			 t = new Tipo();
			 t.setId(rs.getInt("id"));
			 t.setNome(rs.getString("nome"));
		 }
		return t;
	}

	@Override
	public ArrayList<Tipo> listar() throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from tipo;");
		ArrayList<Tipo> ts = new ArrayList<Tipo>();
		while(rs.next()){
			 Tipo t = new Tipo();
			 t.setId(rs.getInt("id"));
			 t.setNome(rs.getString("nome"));
			 ts.add(t);
		}
		return ts;
	}

	@Override
	public ArrayList<Tipo> listar(String nome) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		 String sql = "select * from tipo where lower(nome) like  lower(?) || '%';";
		 PreparedStatement ps = con.prepareStatement(sql);
		 ps.setString(1, nome);
		 ps.execute();
		 ResultSet rs = ps.getResultSet();
		 ArrayList<Tipo> ts = new ArrayList<Tipo>();
		 while(rs.next()){
			 Tipo t = new Tipo();
			 t.setId(rs.getInt("id"));
			 t.setNome(rs.getString("nome"));
			 ts.add(t);
		 }
		 return ts;
	}

}
